import oracledb
import os
import pandas as pd

class SqlPlusSimulator:
    def __init__(self, user, password, dsn):
        # self.connection = cx_Oracle.connect(user, password, dsn)
        # self.cursor = self.connection.cursor()
        self.buffer = ''

    def execute_sql(self, sql):
        try:
            print('执行SQL:', sql)
            # self.cursor.execute(sql)
            # if self.cursor.description:
            #     for row in self.cursor:
            #         print(row)
            # else:
            #     self.connection.commit()
            #     print('操作已提交')
        except cx_Oracle.Error as error:
            print('执行SQL出错:', error)

    def handle_command(self, command, filename=None, line_number=None):
        if command == '/':
            if self.buffer:
                try:
                    self.execute_sql(self.buffer)
                except cx_Oracle.Error as error:
                    print(f'执行SQL出错 (文件: {filename}, 行号: {line_number}):', error)
                self.buffer = ''
            else:
                print('缓冲区为空')
        else:
            self.buffer += command + '\n'


# 示例使用
user = 'your_username'
password = 'your_password'
dsn = 'your_dsn'
simulator = SqlPlusSimulator(user, password, dsn)

# 读取Excel文件
excel_file = 'config.xlsx'  # 替换为你的Excel文件路径
df = pd.read_excel(excel_file).fillna('')
print(df)
# 假设Excel文件中有一列名为'file_path'，包含要执行的SQL文件的路径
for row in df.itertuples():
    sql_file_kind = row.分类  # 只读取第二列（分类为1）
    sql_file_path = row.文件  # 只读取第二列（索引为1）
    sql_file_description = row.说明  # 只读取第三列（索引为2）    
    if os.path.exists(sql_file_path):
        # 读取文件并逐行执行SQL，逐行读取是为了错误的时候可以知道错误的行号
        with open(sql_file_path, 'r') as sql_file:
            for line_number, line in enumerate(sql_file, start=1):
                try:
                    simulator.handle_command(line.strip(), filename=sql_file_path, line_number=line_number)
                except Exception as e:
                    # 如果发生错误，停止执行并打印错误信息
                    desc_info = f"{sql_file_description}引入的{sql_file_kind}" if sql_file_description else ''
                    print(f"编译{desc_info}出错 (文件: {sql_file_path}, 行号: {line_number}): {e}")
                    break
    else:
        desc_info = f"{sql_file_description}引入的{sql_file_kind}" if sql_file_description else ''
        print(f"编译{desc_info}出错, 文件[{sql_file_path}]不存在")
        
# simulator.cursor.close()
# simulator.connection.close()